package com.PMSys.Dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.PMSys.Demain.Staff;
import com.PMSys.Demain.Post;

public class PostDaoImp implements PostDao{
	
	private Connection conn;
	public PostDaoImp(Connection conn){
		this.conn=conn;
	}
	
	
	
	public void addPost(Post posterer) {
		
		String sql = "insert into Post(p_name,p_type,p_compilation) values(?,?,?)";
		
		PreparedStatement pst = null;
		
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1,posterer.getP_name());
			pst.setString(2,posterer.getP_type());
			pst.setString(3,posterer.getP_compilation());
					
			pst.executeUpdate();
			
		} catch (SQLException e) {
		e.printStackTrace();
		}	
	}

	
	public Post selectById(String p_oldId){
		Post Poster=null;
		String sql="select * from Post where p_id = ? order by p_id asc";
	
		PreparedStatement pst=null;
		
		try {			
			pst=conn.prepareStatement(sql);			
		
			
			pst.setString(1, p_oldId);
			
		
			ResultSet rs= pst.executeQuery();
			
			while(rs.next()){
				Poster=new Post();
				Poster.setP_id(rs.getString("P_id"));
				Poster.setP_name(rs.getString("P_name"));
				
				if(rs.getString("P_type").equals("1")){
					Poster.setP_type("管理");
				}
				if(rs.getString("P_type").equals("2")){
					Poster.setP_type("技术");
				}
				if(rs.getString("P_type").equals("3")){
					Poster.setP_type("营销");
				}
				if(rs.getString("P_type").equals("4")){
					Poster.setP_type("市场");
				}
				Poster.setP_compilation(rs.getString("P_compilation"));
			
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Poster;	
		
	}
	
	
	
	public Post getPostId(Post selectPoster) {
		
		Post Poster=null;
		String sql="select * from Post where p_id = ? order by p_id asc";
	
		PreparedStatement pst=null;
		
		try {			
			pst=conn.prepareStatement(sql);			
		
			
			pst.setString(1, selectPoster.getP_id());
			
		
			ResultSet rs= pst.executeQuery();
			
			while(rs.next()){
				Poster=new Post();
				Poster.setP_id(rs.getString("P_id"));
				Poster.setP_name(rs.getString("P_name"));
				
				if(rs.getString("P_type").equals("1")){
					Poster.setP_type("管理");
				}
				if(rs.getString("P_type").equals("2")){
					Poster.setP_type("技术");
				}
				if(rs.getString("P_type").equals("3")){
					Poster.setP_type("营销");
				}
				if(rs.getString("P_type").equals("4")){
					Poster.setP_type("市场");
				}
				Poster.setP_compilation(rs.getString("P_compilation"));
			
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Poster;	
	}



	
	public void updatePost(Post Poster) {
		
		String sql = "update Post set p_name=?,P_type=?,P_compilation=? where p_id=?";
		
		PreparedStatement pst = null;
		
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1,Poster.getP_name());
			pst.setString(2,Poster.getP_type());
			pst.setString(3,Poster.getP_compilation());
			pst.setString(4,Poster.getP_id());
		
			pst.executeUpdate();
			
		} catch (SQLException e) {
		e.printStackTrace();
		}	
	}
	
	
	public void deletePost(String id) {
		
		String sql = "delete from Post where p_id=?";
		
		PreparedStatement pst = null;
		
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, id);
			
			pst.executeUpdate();
			
			System.out.println(sql.toString());
		} catch (SQLException e) {
		e.printStackTrace();
		}	
	}
	

	
	
	public List<Post> getAllPost() {
		
		Post Poster=null;
		List<Post> Posts=new ArrayList<Post>();
		String sql="select * from Post order by p_id asc";
	
		PreparedStatement pst=null;
		
		try {			
			pst=conn.prepareStatement(sql);			
		
			
			ResultSet rs= pst.executeQuery();
			
			while(rs.next()){
				Poster=new Post();
				Poster.setP_id(rs.getString("P_id"));
				Poster.setP_name(rs.getString("P_name"));
				if(rs.getString("P_type").equals("1")){
					Poster.setP_type("管理");
				}
				if(rs.getString("P_type").equals("2")){
					Poster.setP_type("技术");
				}
				if(rs.getString("P_type").equals("3")){
					Poster.setP_type("营销");
				}
				if(rs.getString("P_type").equals("4")){
					Poster.setP_type("市场");
				}
				Poster.setP_compilation(rs.getString("P_compilation"));
				Posts.add(Poster);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Posts;	
	}



	public List<Staff> getPostStaff(Post poster) {
	
	
		Staff staff=null;
		List<Staff> allpoststaff=new ArrayList<Staff>();
		String sql="select * from Staff where p_id = ?";
		
		
		PreparedStatement pst=null;
		
		try {			
			pst=conn.prepareStatement(sql);			
			
			pst.setString(1, poster.getP_id());
			
			
			ResultSet rs= pst.executeQuery();
			
			while(rs.next()){
				staff=new Staff();
				staff.setS_id(rs.getString("S_id"));
				staff.setS_name(rs.getString("S_Name"));
				staff.setD_id(rs.getString("d_Id"));
				
						
				allpoststaff.add(staff);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return allpoststaff;		
	}



	public List<Post> getPostbyName(Post selectPost) {
		
				Post post=null;
				List<Post> allPost=new ArrayList<Post>();
				String sql="select * from Post where p_name like ? order by p_id asc";
				
				PreparedStatement pst=null;
				
				try {			
					pst=conn.prepareStatement(sql);			
				
					
					pst.setString(1, "%"+selectPost.getP_name()+"%");
					
					
					ResultSet rs= pst.executeQuery();
					
					while(rs.next()){
						post=new Post();
						post.setP_id(rs.getString("p_id"));
						post.setP_name(rs.getString("p_name"));
						if(rs.getString("P_type").equals("1")){
							post.setP_type("管理");
						}
						if(rs.getString("P_type").equals("2")){
							post.setP_type("技术");
						}
						if(rs.getString("P_type").equals("3")){
							post.setP_type("营销");
						}
						if(rs.getString("P_type").equals("4")){
							post.setP_type("市场");
						}
						post.setP_compilation(rs.getString("p_Compilation"));
						
					
						allPost.add(post);
					}	
					
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return allPost;	
	}



	public List<Post> getPostbyType(Post selectPost) {
	
		Post post=null;
		List<Post> allPost=new ArrayList<Post>();
		String sql="select * from Post where P_type = ? order by p_id asc";
		
		PreparedStatement pst=null;
		
		try {			
			pst=conn.prepareStatement(sql);								
			pst.setString(1,selectPost.getP_type());					
			ResultSet rs= pst.executeQuery();
			
			while(rs.next()){
				post=new Post();
				post.setP_id(rs.getString("p_id"));
				post.setP_name(rs.getString("p_name"));
				if(rs.getString("P_type").equals("1")){
					post.setP_type("管理");
				}
				if(rs.getString("P_type").equals("2")){
					post.setP_type("技术");
				}
				if(rs.getString("P_type").equals("3")){
					post.setP_type("营销");
				}
				if(rs.getString("P_type").equals("4")){
					post.setP_type("市场");
				}
				post.setP_compilation(rs.getString("p_Compilation"));			
			
				allPost.add(post);
			}	
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allPost;	
	}



	public List<Post> selectByValue(Post post) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from post where 1=1");

		if (post.getP_id() != null && !"".equals(post.getP_id())) {
			sql.append(" and p_id=");
			sql.append(post.getP_id());
		}

		if (post.getP_name() != null && !"".equals(post.getP_name())) {
			sql.append(" and p_name like '%");
			sql.append(post.getP_name());
			sql.append("%'");
		}

		if (Integer.parseInt(post.getP_type()) != 0) {
			sql.append(" and p_type=");
			sql.append(post.getP_type());			
		}
		
		sql.append("order by p_id asc");
 		 
		 
		 PreparedStatement pst=null;
		 List<Post> allpost = new ArrayList<Post>();
			try {			
				pst=conn.prepareStatement(sql.toString());			
							
				ResultSet rs= pst.executeQuery();
				
				while(rs.next()){
					post=new Post();
					post.setP_id(rs.getString("p_id"));
					post.setP_name(rs.getString("p_name"));
					if(rs.getString("P_type").equals("1")){
						post.setP_type("管理");
					}
					if(rs.getString("P_type").equals("2")){
						post.setP_type("技术");
					}
					if(rs.getString("P_type").equals("3")){
						post.setP_type("营销");
					}
					if(rs.getString("P_type").equals("4")){
						post.setP_type("市场");
					}
					post.setP_compilation(rs.getString("p_Compilation"));
							
					allpost.add(post);
				}	
					
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return allpost;		
	}	

}
